﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CFM.DAL;
using CFM.DTO;


namespace CFM.API
{
    public class ProductAPI
    {
        private DataHelper _helper = null;
        public string error = string.Empty;
        public ProductAPI()
        {
            _helper = new DataHelper();
        }
        public List<Product> GetAll()
        {
            string sql = "select * from Product order by UpdateTime desc";
            SqlDataReader dr = _helper.ExcuteDataReader(sql, null);
            return _helper.MapDataReaderToObject<Product>(dr);
        }
        public List<Product> GetProByCateId(string cateId)
        {
            string sql = "select * from Product where CategoryId = '" + cateId + "'  order by UpdateTime desc";
            SqlDataReader dr = _helper.ExcuteDataReader(sql, null);
            return _helper.MapDataReaderToObject<Product>(dr);
        }

        public Product GetProductByProductId(string productId)
        {
            Product pd = new Product();
            string sql = "select * from Product where ProductId = @ProductId  order by UpdateTime desc";
            SqlParameter[] prs = { 
                                     new SqlParameter("@ProductId", productId) 
                                 };
            SqlDataReader dr = _helper.ExcuteDataReader(sql, prs);
            if (dr != null)
                while (dr.Read())
                {
                    pd.ProductId = dr["ProductId"].ToString().Trim();
                    pd.ProductName = dr["ProductName"].ToString().Trim();
                    pd.Unit1Id = dr["Unit1Id"].ToString().Trim();
                    pd.Unit2Id = dr["Unit2Id"].ToString().Trim();
                    if(!string.IsNullOrEmpty(dr["BuyPrice"].ToString()))
                        pd.BuyPrice = Convert.ToInt32(dr["BuyPrice"].ToString());
                }
            _helper.DisConnect();
            return pd;
        }
        public List<Product> GetAllPro()
        {
            List<Product> listpro = new List<Product>();
            string sql = "select *,b.CategoryName from Product a join Categories b on a.categoryid = b.categoryid where a.categoryid = @categoryid  order by UpdateTime desc";
            SqlDataReader dr = _helper.ExcuteDataReader(sql, null);
            return _helper.MapDataReaderToObject<Product>(dr);
        }
        public List<Product> GetProductByGroupId(string groupId)
        {
            string sql = "select *,b.CategoryName from product a join Categories b on a.categoryid = b.categoryid where a.categoryid = @categoryid  order by UpdateTime desc";
            SqlParameter[] prs = 
            {
                new SqlParameter("@categoryid",groupId)
            };
            SqlDataReader dr = _helper.ExcuteDataReader(sql, prs);
            return _helper.MapDataReaderToObject<Product>(dr);
        }
        public bool Create(Product product)
        {
            string sql = "if not exists (select * from Product where ProductId = @ProductId) ";
            sql += " insert into Product(ProductId,CategoryId,ProductName,Unit1Id,Unit2Id,Rate,BuyPrice,SalePrice,Quantity,Description,UpdateTime) values(@ProductId,@CategoryId,@ProductName,@Unit1Id,@Unit2Id,@Rate,@BuyPrice,@SalePrice,@Quantity,@Description,@UpdateTime)";
            SqlParameter[] prs = 
            {
                new SqlParameter("@ProductId",SqlDbType.Char),
                new SqlParameter("@ProductName",SqlDbType.NVarChar),
                new SqlParameter("@CategoryId",SqlDbType.Char),
                new SqlParameter("@Unit1Id",SqlDbType.Char),
                new SqlParameter("@Unit2Id",SqlDbType.Char),
                new SqlParameter("@Rate",SqlDbType.Float ),
                new SqlParameter("@BuyPrice",SqlDbType.Float),
                new SqlParameter("@SalePrice",SqlDbType.Float),
                new SqlParameter("@Quantity",SqlDbType.Int),
                new SqlParameter("@Description",SqlDbType.NVarChar),
                new SqlParameter("@UpdateTime",SqlDbType.DateTime),
            };
            prs[0].Value = product.ProductId;
            prs[1].Value = product.ProductName;
            prs[2].Value = product.CategoryId;
            if (!product.Unit1Id.Equals("-1"))
                prs[3].Value = product.Unit1Id;
            if(!product.Unit2Id.Equals("-1"))
                prs[4].Value = product.Unit2Id;
            prs[5].Value = product.Rate;
            prs[6].Value = product.BuyPrice;
            prs[7].Value = product.SalePrice;
            prs[8].Value = product.Quantity;
            prs[9].Value = product.Description;
            prs[10].Value = product.UpdateTime;
            bool rs =  _helper.ExcuteNonQuery(sql, prs);
            error = _helper.error;
            return rs;
        }
        public bool Update(Product product)
        {
            string sql = " update Product set CategoryId=@CategoryId,ProductName=@ProductName,Unit1Id=@Unit1Id,Unit2Id=@Unit2Id,Rate=@Rate,BuyPrice=@BuyPrice,SalePrice=@SalePrice,Quantity=@Quantity,Description=@Description,UpdateTime=@UpdateTime where ProductId=@ProductId";
            SqlParameter[] prs = 
            {
                new SqlParameter("@ProductId",product.ProductId),
                new SqlParameter("@ProductName",product.ProductName),
                new SqlParameter("@CategoryId",product.CategoryId),
                new SqlParameter("@Unit1Id",product.Unit1Id),
                new SqlParameter("@Unit2Id",product.Unit2Id),
                new SqlParameter("@Rate",product.Rate),
                new SqlParameter("@BuyPrice",product.BuyPrice),
                new SqlParameter("@SalePrice",product.SalePrice),
                new SqlParameter("@Quantity",product.Quantity),
                new SqlParameter("@Description",product.Description),
                new SqlParameter("@UpdateTime",product.UpdateTime),
            };
            bool rs = _helper.ExcuteNonQuery(sql, prs);
            error = _helper.error;
            return rs;
        }
        public bool Delete(string productId)
        {
            string sql = "delete FormulaProcess where Productid = @Productid; delete product where Productid = @Productid";
            SqlParameter[] prs = 
            {
                new SqlParameter("@Productid",productId)
            };
            bool rs = _helper.ExcuteNonQuery(sql, prs);
            error = _helper.error;
            return rs;               
        }
    }
}
